import pandas as pd
import xgboost as xgb
from sklearn.preprocessing import MinMaxScaler
from sqlalchemy import create_engine
import sys
import time


def importance(district, choose_x, choose_y):
    data_name = 'data_' + district
    engine = create_engine('mysql+pymysql://app_user:123456@localhost:3307/smart_cim?charset=utf8')
    dataframes = pd.read_sql(data_name, engine)


    #转换数据类型
    col_names = list(dataframes)
    # print(col_names)
    for col in col_names:
        dataframes[col] = dataframes[col].astype('float', copy=False)
    # print(dataframes.dtypes)

    # 归一化处理
    #2.实例化一个转换器类
    transfer = MinMaxScaler(feature_range=(1,100))
    # 3.调用fit_transform()
    xi = transfer.fit_transform(dataframes)
    # 4、转化为二维表
    train_data = pd.DataFrame(xi, columns=dataframes.columns)

    ##选择预测对象
    # choose = 'house_price'
    del_feature = ['id', 'year', choose_y]
    features = [i for i in choose_x]

    ##生成特征表
    target_data = train_data[choose_y]
    # print(type(target_data))
    features_data = train_data[features]

    # train_x, test_x, train_y, test_y = train_test_split(features_data, target_data, test_size = 0.2, random_state=2000)
    train_x = features_data.iloc[:203, :]
    # print(train_x)
    train_y = target_data[:203]
    # print(train_y)
    test_x = features_data.iloc[[203], :]
    # print(test_x)

    model_r = xgb.XGBRegressor(max_depth=6,
                            learning_rate=0.1,
                            n_estimators=100,
                            objective='reg:squarederror', # 此默认参数与 XGBClassifier 不同
                            booster='gbtree',
                            gamma=0,
                            min_child_weight=1,
                            subsample=1,
                            colsample_bytree=0.5,
                            reg_alpha=0,
                            reg_lambda=1,
                            random_state=0)
    model_r.fit(train_x, train_y)  # 使用训练数据训练
    model_r.save_model('xgb104.model') #保存模型
    model= xgb.Booster(model_file='xgb104.model') #模型加载
    # print(X_test)
    X_test1 = xgb.DMatrix(test_x) #数据转化
    # print(X_test1)
    fit_pred1 = model.predict(X_test1) # 预测
    # print(fit_pred1)

    #重要度排序
    fold_importance_df = pd.DataFrame()
    fold_importance_df["feature"] = model.get_fscore().keys()
    fold_importance_df["value"] = model.get_fscore().values()
    # fold_importance_df.to_sql('feature_importance',
    #                           db,
    #                           if_exists='replace',
    #                           index=True,
    #                           dtype={})
    # print(model.get_fscore().values())
    # print(fold_importance_df)

    return fold_importance_df
# dataframes.loc[0, 'SendResult'] = '002'
# fold_importance_df.to_sql('feature_importance', engine, index=False)

# cols = (fold_importance_df[["Feature", "importance"]].groupby("Feature").mean().sort_values(by="importance", ascending=False).index)
# best_features = fold_importance_df.loc[fold_importance_df.Feature.isin(cols[:50])].sort_values(by='importance',ascending=False)


if __name__ == '__main__':

    print('ssssssssssssssssssss')
    list_str = []
    # list_str = ['企业数', '消费数', '人口数', 'data_pudong']

    print(str(sys.argv[1:]))
    for i in range(1, len(sys.argv)):
        list_str.append(sys.argv[i].replace(",", ""))
    # 处理第一个还有最后一个元素的格式
    list_str[0] = list_str[0].replace("[", "")
    # list_str[len(sys.argv) - 2] = list_str[len(sys.argv) - 2].replace("]", "")
    list_end = list_str[len(sys.argv) - 2].split(']')
    list_str[len(sys.argv) - 2] = list_end[0]
    list_str.append(list_end[1])
    print(list_str)
    n = len(list_str)

    # 将中文转化为英文（数据库中字段为英文）
    for i in range(n):
        if list_str[i] == '企业数':
            list_str[i] = 'company'
        if list_str[i] == '消费数':
            list_str[i] = 'consume'
        if list_str[i] == '资本量':
            list_str[i] = 'capital'
        if list_str[i] == '区域市价':
            list_str[i] = 'house_price'
        if list_str[i] == '雇员数':
            list_str[i] = 'employees'
        if list_str[i] == '人口数':
            list_str[i] = 'population'

    all_district = ['baoshan', 'changning', 'chongming', 'fengxian',
                    'hongkou', 'huangpu', 'jiading', 'jingan', 'jinshan', 'minhang',
                    'pudong', 'putuo', 'qingpu', 'songjiang', 'xuhui', 'yangpu']
    choose_x = list_str[0:n-2]
    choose_y = list_str[n-2]
    print(choose_x)
    all_result = pd.DataFrame({'feature': [], 'value': [], 'district': []})

    start = time.time()
    for i in range(0, len(all_district)):
        district = all_district[i]
        result = importance(district, choose_x, choose_y)
        result['district'] = district
        all_result = pd.concat([all_result, result], axis=0, ignore_index=True)
    end = time.time()
    print(all_result)

    # result_path = './result.xlsx'
    # all_result.to_excel(result_path,index = False)
    engine = create_engine('mysql+pymysql://app_user:123456@localhost:3307/smart_cim?charset=utf8')
    all_result.to_sql(name='feature_importance', con=engine, if_exists='replace')
    print("importance is finished!")


